package com.seeyon.apps.brwh.service;

import com.seeyon.apps.brwh.kit.DBKit;
import com.seeyon.apps.brwh.kit.FormCap4Kit;
import com.seeyon.apps.brwh.kit.StrKit;
import com.seeyon.cap4.form.bean.FormBean;
import com.seeyon.cap4.form.bean.FormFieldBean;
import com.seeyon.cap4.form.bean.FormTableBean;
import com.seeyon.cap4.form.service.CAP4FormManager;
import com.seeyon.ctp.common.AppContext;
import com.seeyon.ctp.common.exceptions.BusinessException;
import com.seeyon.ctp.common.log.CtpLogFactory;
import com.seeyon.ctp.organization.manager.OrgManager;

import org.apache.commons.logging.Log;

import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 采购成本率计算
 */
public class SRMKanBanCgcblService {

    private static final Log LOG = CtpLogFactory.getLog(SRMKanBanCgcblService.class);


    private static final String cgcbldabTempCode = AppContext.getSystemProperty("brwh.cgcbldabTempCode");

    private CAP4FormManager cap4FormManager = (CAP4FormManager) AppContext.getBean("cap4FormManager");
    private static OrgManager orgManager = (OrgManager) AppContext.getBean("orgManager");


    /**
     * 根据公司获取采购成本率
     *
     * @return
     */
    public Map<String, Object> getCGCBLByGS() {
        Map<String, Object> resultMap = new HashMap<>();
        resultMap.put("cbl", "");
        resultMap.put("tb", "");
        FormBean formBean = null;
        try {
            formBean = cap4FormManager.getFormByFormCode(cgcbldabTempCode);
        } catch (BusinessException e) {
            LOG.info("根据采购成本率档案模板编码获取采购成本率表信息异常，采购成本率档案模板：" + cgcbldabTempCode + "：", e);
            return resultMap;
        }
        FormTableBean masterTableBean = formBean.getMasterTableBean();

        //获取字段信息
        FormFieldBean xmfyFieldBean = formBean.getFieldBeanByDisplay("项目费用");
        FormFieldBean xmxssrFieldBean = formBean.getFieldBeanByDisplay("项目销售收入");
        FormFieldBean xmclcbFieldBean = formBean.getFieldBeanByDisplay("项目材料成本");
        FormFieldBean gscgcblFieldBean = formBean.getFieldBeanByDisplay("公司采购成本率");
        FormFieldBean tjnyFieldBean = formBean.getFieldBeanByDisplay("统计年月");
        FormFieldBean xmbmFieldBean = formBean.getFieldBeanByDisplay("项目编码");

        //StringBuilder sqlb = new StringBuilder("select ");
        //sqlb.append("MAX(").append(gscgcblFieldBean.getName()).append(") plcgcbl,");
        //sqlb.append("((SUM(").append(xmfyFieldBean.getName()).append(") + SUM(").append(xmclcbFieldBean.getName()).append(")) / SUM(").append(xmxssrFieldBean.getName()).append(")) cbl");
        //sqlb.append(" FROM ").append(masterTableBean.getTableName());
        //sqlb.append(" WHERE DATEPART(YEAR, ").append(tjnyFieldBean.getName()).append(") = DATEPART(YEAR, DATEADD(MONTH, -1, GETDATE()))");
        //sqlb.append(" AND DATEPART(MONTH, ").append(tjnyFieldBean.getName()).append(") = DATEPART(MONTH, DATEADD(MONTH, -1, GETDATE()))");
        // 获取字段信息
        StringBuilder sqlb = new StringBuilder("SELECT ");
        //sqlb.append(xmbmFieldBean.getName()).append(", ");
        sqlb.append("MAX(").append(gscgcblFieldBean.getName()).append(") plcgcbl, ");
        sqlb.append("((");
        sqlb.append("(SELECT SUM(max_xmfy) FROM (SELECT ").append(xmbmFieldBean.getName()).append(", SUM(").append(xmfyFieldBean.getName()).append(") AS max_xmfy FROM ").append(masterTableBean.getTableName()).append(" WHERE DATEPART(YEAR, ").append(tjnyFieldBean.getName()).append(") = DATEPART(YEAR, DATEADD(MONTH, -1, GETDATE())) AND DATEPART(MONTH, ").append(tjnyFieldBean.getName()).append(") = DATEPART(MONTH, DATEADD(MONTH, -1, GETDATE())) GROUP BY ").append(xmbmFieldBean.getName()).append(") t1) + ");
        sqlb.append("(SELECT SUM(max_xmclcb) FROM (SELECT ").append(xmbmFieldBean.getName()).append(", SUM(").append(xmclcbFieldBean.getName()).append(") AS max_xmclcb FROM ").append(masterTableBean.getTableName()).append(" WHERE DATEPART(YEAR, ").append(tjnyFieldBean.getName()).append(") = DATEPART(YEAR, DATEADD(MONTH, -1, GETDATE())) AND DATEPART(MONTH, ").append(tjnyFieldBean.getName()).append(") = DATEPART(MONTH, DATEADD(MONTH, -1, GETDATE())) GROUP BY ").append(xmbmFieldBean.getName()).append(") t2) ");
        sqlb.append(") / ");
        sqlb.append("(SELECT SUM(max_xmxssr) FROM (SELECT ").append(xmbmFieldBean.getName()).append(", SUM(").append(xmxssrFieldBean.getName()).append(") AS max_xmxssr FROM ").append(masterTableBean.getTableName()).append(" WHERE DATEPART(YEAR, ").append(tjnyFieldBean.getName()).append(") = DATEPART(YEAR, DATEADD(MONTH, -1, GETDATE())) AND DATEPART(MONTH, ").append(tjnyFieldBean.getName()).append(") = DATEPART(MONTH, DATEADD(MONTH, -1, GETDATE())) GROUP BY ").append(xmbmFieldBean.getName()).append(") t3) ");
        sqlb.append(") cbl ");
        sqlb.append("FROM ").append(masterTableBean.getTableName());
        sqlb.append(" WHERE DATEPART(YEAR, ").append(tjnyFieldBean.getName()).append(") = DATEPART(YEAR, DATEADD(MONTH, -1, GETDATE())) ");
        sqlb.append("AND DATEPART(MONTH, ").append(tjnyFieldBean.getName()).append(") = DATEPART(MONTH, DATEADD(MONTH, -1, GETDATE())) ");
        LOG.info("根据公司获取成本率sql语句：" + sqlb);
        try {
            List<Map<String, Object>> maps = DBKit.excuteSQL(sqlb.toString(), null);
            //获取跳转报表地址
            String url = FormCap4Kit.getBbAddres("采购成本率-公司");
            for (Map<String, Object> map : maps) {
                float plcgcblF = 0;
                float cblF = 0;
                String plcgcbl = StrKit.str(map.get("plcgcbl"));
                String cbl = StrKit.str(map.get("cbl"));
                if (!plcgcbl.equals("")) {
                    plcgcblF = Float.parseFloat(plcgcbl);
                }
                if (!cbl.equals("")) {
                    cblF = Float.parseFloat(cbl);
                }
                float v = cblF - plcgcblF;
                DecimalFormat dformat = new DecimalFormat("0.0");
                map.put("cbl",dformat.format(cblF*100));
                map.put("tb", dformat.format(v*100));
//        map.put("tb", v);
                map.put("url", url);
                resultMap = map;
            }
        } catch (Exception e) {
            LOG.info("根据品类获取成本率sql执行异常：", e);
        }
        return resultMap;
    }


    /**
     * 根据品类获取采购成本率
     *
     * @return
     */
    public List<Map<String, Object>> getCGCBLByPL() {
        ArrayList<Map<String, Object>> resultMapList = new ArrayList<>();
        FormBean formBean = null;
        try {
            formBean = cap4FormManager.getFormByFormCode(cgcbldabTempCode);
        } catch (BusinessException e) {
            LOG.info("根据采购成本率档案模板编码获取采购成本率表信息异常，采购成本率档案模板：" + cgcbldabTempCode + "：", e);
            return resultMapList;
        }
        FormTableBean masterTableBean = formBean.getMasterTableBean();

        //获取字段信息
        FormFieldBean plbmFieldBean = formBean.getFieldBeanByDisplay("品类编码");
        FormFieldBean plmcFieldBean = formBean.getFieldBeanByDisplay("品类名称");
        FormFieldBean cggcsFieldBean = formBean.getFieldBeanByDisplay("采购工程师");

        FormFieldBean cpclcbFieldBean = formBean.getFieldBeanByDisplay("成品材料成本");
        FormFieldBean cpxssrFieldBean = formBean.getFieldBeanByDisplay("成品销售收入");
        FormFieldBean plcgcblFieldBean = formBean.getFieldBeanByDisplay("品类采购成本率");
        FormFieldBean tjnyFieldBean = formBean.getFieldBeanByDisplay("统计年月");

        // 组装SQL
        StringBuilder sqlb = new StringBuilder("select top 10 ");
        sqlb.append(plbmFieldBean.getName()).append(" plbm,");
        sqlb.append(plmcFieldBean.getName()).append(" plmc,");
        sqlb.append("MAX(om.NAME) cggcs,");
        sqlb.append("MAX(").append(plcgcblFieldBean.getName()).append(") plcgcbl,");
        sqlb.append("SUM(").append(cpclcbFieldBean.getName()).append(") cpclcb,");
        sqlb.append("SUM(").append(cpxssrFieldBean.getName()).append(") cpxssr,");
        sqlb.append("(SUM(").append(cpclcbFieldBean.getName()).append(") / SUM(").append(cpxssrFieldBean.getName()).append(")) cbl");
        sqlb.append(" FROM ").append(masterTableBean.getTableName()).append(" fm LEFT JOIN ORG_MEMBER om ON fm.").append(cggcsFieldBean.getName()).append(" = om.ID");
        sqlb.append(" WHERE ").append(plbmFieldBean.getName()).append(" IS NOT NULL");
        sqlb.append(" AND DATEPART(YEAR, ").append(tjnyFieldBean.getName()).append(") = DATEPART(YEAR, DATEADD(MONTH, -1, GETDATE()))");
        sqlb.append(" AND DATEPART(MONTH, ").append(tjnyFieldBean.getName()).append(") = DATEPART(MONTH, DATEADD(MONTH, -1, GETDATE()))");
        sqlb.append(" GROUP BY ").append(plbmFieldBean.getName()).append(", ").append(plmcFieldBean.getName());
        sqlb.append(" HAVING SUM(").append(cpxssrFieldBean.getName()).append(") > 0");
        sqlb.append(" ORDER BY cbl");
        LOG.info("根据品类获取成本率sql语句1：" + sqlb);
        try {
            List<Map<String, Object>> maps = DBKit.excuteSQL(sqlb.toString(), null);
            //获取跳转报表地址
            String url = FormCap4Kit.getBbAddres("采购成本率-品类");
            for (Map<String, Object> map : maps) {
                float plcgcblF = 0;
                float cblF = 0;
                String plcgcbl = StrKit.str(map.get("plcgcbl"));
                String cbl = StrKit.str(map.get("cbl"));
                if (!plcgcbl.equals("")) {
                    plcgcblF = Float.parseFloat(plcgcbl);
                }
                if (!cbl.equals("")) {
                    cblF = Float.parseFloat(cbl);
                }
                float v = cblF - plcgcblF;
                int status = 1;
                if (v < 0) {
                    status = 2;
                }
                DecimalFormat dformat = new DecimalFormat("0.0");
                map.put("cbl",dformat.format(cblF*100));
                map.put("tb", dformat.format(v*100));
                map.put("status", status);
                map.put("url", url);
                resultMapList.add(map);
            }
        } catch (Exception e) {
            LOG.info("根据品类获取成本率sql执行异常：", e);
        }
        return resultMapList;
    }


    /**
     * 根据采购工程师获取采购成本率
     *
     * @return
     */
    public List<Map<String, Object>> getCGCBLByCGGCS() {
        ArrayList<Map<String, Object>> resultMapList = new ArrayList<>();
        FormBean formBean = null;
        try {
            formBean = cap4FormManager.getFormByFormCode(cgcbldabTempCode);
        } catch (BusinessException e) {
            LOG.info("根据采购成本率档案模板编码获取采购成本率表信息异常，采购成本率档案模板：" + cgcbldabTempCode + "：", e);
            return resultMapList;
        }
        FormTableBean masterTableBean = formBean.getMasterTableBean();

        //获取字段信息
        FormFieldBean cggcsFieldBean = formBean.getFieldBeanByDisplay("采购工程师");
        FormFieldBean cpclcbFieldBean = formBean.getFieldBeanByDisplay("成品材料成本");

        FormFieldBean xmclcbFieldBean = formBean.getFieldBeanByDisplay("项目材料成本");
        FormFieldBean xmfyFieldBean = formBean.getFieldBeanByDisplay("项目费用");
        FormFieldBean xmxssrFieldBean = formBean.getFieldBeanByDisplay("项目销售收入");

        FormFieldBean cpxssrFieldBean = formBean.getFieldBeanByDisplay("成品销售收入");
        FormFieldBean cggcscgcblFieldBean = formBean.getFieldBeanByDisplay("采购工程师采购成本率");
        FormFieldBean tjnyFieldBean = formBean.getFieldBeanByDisplay("统计年月");
        //获取当前年
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
        String year = sdf.format(new Date());
        //获取每个月份
        String[] months = {"01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12"};
        //组装sql
        //select om.NAME cggcs,datename(mm,field0001) tjy,datename(yy,field0001) tjn,MAX(field0034) cggcscgcbl,sum(field0025) cpclcb,
        // sum(field0010) cpxssr,(sum(field0025) /sum(field0010)) cbl from formmain_3075 fm left join ORG_MEMBER om
        // on fm.field0029 = om.ID where datename(yy,field0001)=2024 AND om.NAME is not null GROUP BY om.NAME,field0001
        StringBuilder sqlb = new StringBuilder("select ");
        sqlb.append("om.NAME cggcs,");
        sqlb.append("datename(mm," + tjnyFieldBean.getName() + ") tjy,");
        sqlb.append("avg(" + cggcscgcblFieldBean.getName() + ") cggcscgcbl,");
        sqlb.append("sum(" + cpclcbFieldBean.getName() + ") cpclcb,");
        sqlb.append("sum(" + cpxssrFieldBean.getName() + ") cpxssr,");
//        sqlb.append("(sum(" + cpclcbFieldBean.getName() + ") /sum(" + cpxssrFieldBean.getName() + ")) cbl");
//        sqlb.append(" (((sum("+xmclcbFieldBean.getName()+") + sum("+xmfyFieldBean.getName()+"))/sum("+xmxssrFieldBean.getName()+"))-(sum(" + cpclcbFieldBean.getName() + ") /sum(" + cpxssrFieldBean.getName() + "))) cbl");
        sqlb.append(" (sum(" + cpclcbFieldBean.getName() + ") /sum(" + cpxssrFieldBean.getName() + ")) cbl");
        sqlb.append(" from " + masterTableBean.getTableName() + " fm left join ORG_MEMBER om on fm." + cggcsFieldBean.getName() + " = om.ID");
        sqlb.append(" where datename(yy," + tjnyFieldBean.getName() + ") = " + year);
        sqlb.append(" AND om.NAME is not null ");
        sqlb.append(" GROUP BY om.NAME," + tjnyFieldBean.getName());
        sqlb.append(" HAVING SUM("+xmxssrFieldBean.getName()+") > 0 and SUM("+cpxssrFieldBean.getName()+") > 0");
        LOG.info("根据采购工程师获取成本率sql语句：" + sqlb);
        try {
            List<Map<String, Object>> maps = DBKit.excuteSQL(sqlb.toString(), null);
            //获取跳转报表地址
            String url = FormCap4Kit.getBbAddres("采购成本率-采购工程师");
            //获取采购工程师
            Map<Object, List<Map<String, Object>>> cggcs = maps.stream().collect(Collectors.groupingBy(map -> map.get("cggcs")));
            for (Object key : cggcs.keySet()) {
                //获取每月的数据
                ArrayList<Map<String, Object>> cggcsMonthsList = new ArrayList<>();
                List<Map<String, Object>> list = cggcs.get(key);
                for (String month : months) {
                    List<Map<String, Object>> tjy = list.stream().filter(m -> m.get("tjy").equals(month)).collect(Collectors.toList());
                    float tb = 0;
                    if (!tjy.isEmpty()) {
                        String cbl = StrKit.str(tjy.get(0).get("cbl"));
                        String cggcscgcbl = StrKit.str(tjy.get(0).get("cggcscgcbl"));
                        float cblF = 0;
                        float cggcscgcblF = 0;
                        if (!cggcscgcbl.equals("")) {
                            cggcscgcblF = Float.parseFloat(cggcscgcbl);
                        }
                        if (!cbl.equals("")) {
                            cblF = Float.parseFloat(cbl);
                        }
                        tb = cblF - cggcscgcblF;
                    }

                    Map<String, Object> monthMap = new HashMap<>();
                    monthMap.put("month", month);
                    DecimalFormat dformat = new DecimalFormat("0.0");
                    monthMap.put("tb",dformat.format(tb*100));
                    monthMap.put("url",url);
                    cggcsMonthsList.add(monthMap);
                }
                Map<String, Object> cggcsMap = new HashMap<>();
                cggcsMap.put("cggcs", key);
                cggcsMap.put("cggcscgcbl", cggcsMonthsList);
                resultMapList.add(cggcsMap);
            }
        } catch (Exception e) {
            LOG.info("根据品类获取成本率sql执行异常：", e);
        }
        return resultMapList;
    }


}

